package com.wb.code14JDBC;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


/**
 * @author 黄衡熙
 * @version 1.0
 */
public class Test01_快速入门 {
    public static void main(String[] args) {
        // User user = new User("admin", "123456", "13812345678", "江苏-南京");
        // addUser(user);
       /* List<User> users = selectAll();
        for (User user : users) {
            System.out.println(user);
        }*/
        //deleteById(1);
        selectById(2);
    }

    public static void addUser(User user) {
        // 通过JDBC访问数据库,主要分为六步
        Connection conn = null;
        Statement st = null;
        try {
            // 1.注册驱动
            // 根据Class.forName()方法注册对应驱动,其参数为驱动类的包名.类名
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2.获取连接,通过访问数据库厂商提供的url地址以及数据库的账号密码进行连接
            // 不同数据库厂商提供的url地址也不同
            // public static Connection getConnection(String url,String user, String password)
            // MySQL的url地址格式为{jdbc:mysql://ip地址:端口号/库名}
            // MySQL默认端口是3306
            // conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc", "root", "root");
            // 在Java访问数据库的时候,可能会存在乱码,为了防止乱码,可以在url地址的末尾添加指定编码
            // ?useUnicode=true&characterEncoding=utf-8
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8", "root", "root");

            // 3.获取状态集,用于操作数据库
            st = conn.createStatement();

            // 4.执行sql
            // 准备一个sql语句
            String sql = "insert into t_user(username,password,phone,address) values('" + user.getUsername() + "','" + user.getPassword() + "','" + user.getPhone() + "','" + user.getAddress() + "')";
            // 执行DML操作,即:inset、delete、update
            // 方法返回值表示受到影响的行数
            int rows = st.executeUpdate(sql);
            System.out.println("Query OK, " + rows + " row affected (0.01 sec)");

            // 第五步:行映射,将数据库查询结果映射成Java对象的数据
            // 对于DML操作,没有第五步
            // 对于查询操作,存在第五步
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 6.释放资源
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    //查询
    public static List<User> selectAll() {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        List<User> users = new ArrayList<>();
        try {
            //1.注册
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode&characterEncoding=utf-8", "root", "qq670892");
            //3.获取状态集
            st = conn.createStatement();
            //4.执行SQL查询并存在返回值
            rs = st.executeQuery("select * from t_user");//此处采用简写的方式把执行查询和存在返回值写到了一起
            //5.行映射
            while (rs.next()) {
                User user = new User();
                int id = rs.getInt("id");
                System.out.println("id : " + id);
                user.setId(id);
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                users.add(user);
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return users;
    }

    //修改
    public static void updateUser(User user) {
        Connection conn = null;
        Statement st = null;
        try {
            // 1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2.获取连接
            conn = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8",
                    "root",
                    "qq670892"
            );

            // 3.获取状态集
            st = conn.createStatement();

            // 4.执行SQL
            String sql = "update t_user set " +
                    "username='" + user.getUsername() + "'," +
                    "password='" + user.getPassword() + "'," +
                    "phone='" + user.getPhone() + "'," +
                    "address='" + user.getAddress() + "' " +
                    "where id=" + user.getId();
            int rows = st.executeUpdate(sql);
            System.out.println("Query OK, " + rows + " row affected (0.01 sec)");

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 6.释放资源
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    public static void deleteById(Integer id) {
        Connection conn = null;
        Statement st = null;
        try {
            // 1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2.获取连接
            conn = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8",
                    "root",
                    "qq670892"
            );

            // 3.获取状态集
            st = conn.createStatement();

            // 4.执行SQL
            String sql = "delete from t_user where id=" + id;
            int rows = st.executeUpdate(sql);
            System.out.println("Query OK, " + rows + " row affected (0.01 sec)");

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 6.释放资源
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    // 根据ID查询用户
    public static User selectById(Integer id) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        User user = null;
        try {
            // 1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2.获取连接
            conn = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8",
                    "root",
                    "qq670892"
            );

            // 3.获取状态集
            st = conn.createStatement();

            // 4.执行SQL
            String sql = "select id,username,password,phone,address from t_user where id=" + id;
            rs = st.executeQuery(sql);

            // 5.行映射
            if (rs.next()) {  // 按ID查询最多一条记录，用if即可
                user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
            }

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 6.释放资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return user;
    }
}


